package com.plian.system.service.sys.excel.impl;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.plian.Tools.*;
import com.plian.system.config.exception.MyRuntimeException;
import com.plian.system.constant.sys.AutoExcelConstant;
import com.plian.system.constant.sys.FormStatusConstant;
import com.plian.system.constant.sys.FormTypeConstant;
import com.plian.system.constant.yth.YthConstant;
import com.plian.system.entity.sys.Org;
import com.plian.system.entity.sys.excel.*;
import com.plian.system.mapper.sys.excel.AutoExcelMapper;
import com.plian.system.mapper.sys.excel.ExcelColumnMapper;
import com.plian.system.mapper.sys.excel.ExcelFormLinkMapper;
import com.plian.system.mapper.sys.excel.ExcelFormMapper;
import com.plian.system.service.sys.excel.IAutoExcelService;
import com.plian.system.service.sys.orgzation.IOrgService;
import com.plian.system.service.yth.helper.YthExcelUtils;
import com.plian.system.vo.yth.YthExcelInfoVO;
import com.plian.system.vo.yth.YthResult;
import lombok.AllArgsConstructor;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Service
@AllArgsConstructor
public class AutoExcelServiceImpl extends ServiceImpl<AutoExcelMapper, AutoExcel> implements IAutoExcelService {

    private final IOrgService orgService;

    private final ExcelFormMapper excelFormMapper;

    private final ExcelColumnMapper excelColumnMapper;

    private final ExcelFormLinkMapper excelFormLinkMapper;

    private final static String AUTO_EXCEL_BILL_START_NAME = "excel_bill_";

    private final static String AUTO_EXCEL_VALUE_START_NAME = "excel_value_";

    private final static String PAYROLL_SETTLEMENT_SHEET_NAME = "表11工资总额清算表 ";

    private final List<String> NOT_UPDATE_STATUS = Stream.of(FormStatusConstant.FLOW + "", FormStatusConstant.FINISH + "").collect(Collectors.toList());

    @Override
    public AutoExcel findByCodeAndYear(String code, int year) {
        List<AutoExcel> list = list(Wrappers.<AutoExcel>lambdaQuery().eq(AutoExcel::getCode, code));
        if (CollectionUtil.isEmpty(list)){
            return null;
        }

        if (list.size() == 1){
            return list.get(0);
        }
        for (AutoExcel autoExcel : list){
            if (StringUtil.isBlank(autoExcel.getCreateTime()) || StringUtil.isBlank(autoExcel.getEndTime())){
                continue;
            }
            int startYear = Integer.valueOf(autoExcel.getStartTime().split(StringPool.DASH)[0]);
            int endYear = Integer.valueOf(autoExcel.getEndTime().split(StringPool.DASH)[0]);
            if (year >= startYear && year <= endYear){
                return autoExcel;
            }
        }
        return null;
    }

    @Override
    public String ythPayrollSettlementExcelUpload(YthExcelInfoVO ythExcelInfoVO, MultipartFile multipartFile) {
        if (multipartFile == null){
            return JSON.toJSONString(YthResult.builder().failReason("文件为空!"));
        }
        Org org = orgService.getByDataSourceId(YthConstant.DATA_NINGBO, ythExcelInfoVO.getOrgId());
        if (org == null){
            return JSON.toJSONString(YthResult.builder().failReason(StringUtil.format("orgId = {} ,该单位不存在!", ythExcelInfoVO.getOrgId())));
        }

        int year = Integer.valueOf(ythExcelInfoVO.getReportTime());
        AutoExcel autoExcel = findByCodeAndYear(AutoExcelConstant.JZB_CODE, year);
        if (autoExcel == null){
            return JSON.toJSONString(YthResult.builder().failReason("文件配置未找到!"));
        }
        ExcelForm excelForm = excelFormMapper.selectOne(Wrappers.<ExcelForm>lambdaQuery()
                .eq(ExcelForm::getExcelId, autoExcel.getId())
                .eq(ExcelForm::getName, PAYROLL_SETTLEMENT_SHEET_NAME));
        if (excelForm == null){
            return JSON.toJSONString(YthResult.builder().failReason("文件Sheet配置未找到!"));
        }

        List<ExcelColumn> excelColumnList = excelColumnMapper.selectList(Wrappers.<ExcelColumn>lambdaQuery()
                .eq(ExcelColumn::getFormId, excelForm.getFormId())
                .orderByAsc(ExcelColumn::getOrdernumber));
        if (CollectionUtil.isEmpty(excelColumnList)){
            return JSON.toJSONString(YthResult.builder().failReason("文件列配置未找到!"));
        }

        String tableName = AUTO_EXCEL_BILL_START_NAME + autoExcel.getTableName();
        String valueTableName = AUTO_EXCEL_VALUE_START_NAME + autoExcel.getTableName();
        List<ExcelBill> billList = getBaseMapper().findByOrgIdAndTableName(tableName, org.getId(), autoExcel.getId());
        Map<String, ExcelBill> excelBillMap = billList.stream().collect(Collectors
                .toMap(excelBill -> DateUtil.format(excelBill.getReportTime(), DateUtil.PATTERN_YEAR), excelBill -> excelBill));
        ExcelBill excelBill;
        ExcelFormLink excelFormLink;
        if (excelBillMap.containsKey(ythExcelInfoVO.getReportTime())){
            excelBill = excelBillMap.get(ythExcelInfoVO.getReportTime());
            if (NOT_UPDATE_STATUS.contains(excelBill.getStatus())){
                return JSON.toJSONString(YthResult.builder().failReason("该单据流程中或已审批，不能修改!"));
            }
            getBaseMapper().updatePayrollSettlementId(tableName, excelBill.getId(), ythExcelInfoVO.getFormId());
            excelFormLink = excelFormLinkMapper.selectOne(Wrappers.<ExcelFormLink>lambdaQuery()
                    .eq(ExcelFormLink::getFormId, excelForm.getExcelId()));
            if (excelFormLink == null){
                excelFormLink = new ExcelFormLink();
                excelFormLink.setId(excelBill.getId());
                excelFormLink.setFormId(excelForm.getFormId());
                excelFormLinkMapper.insert(excelFormLink);
            }else {
                getBaseMapper().deleteValueByLinkIdAndTableName(excelFormLink.getLinkId(), valueTableName);
            }
        }else {
            excelBill = new ExcelBill();
            excelBill.setId(UuidUtil.get32UUID());
            excelBill.setOrgId(org.getId());
            excelBill.setStatus(FormStatusConstant.UNREVIEWED + "");
            excelBill.setFormType(FormTypeConstant.SYS_AUTO_EXCEL + "");
            excelBill.setReportTime(DateUtil.parse(ythExcelInfoVO.getReportTime() + "-01-01", DateUtil.PATTERN_DATE));
            excelBill.setApplicant(ythExcelInfoVO.getInformant());
            excelBill.setExcelId(autoExcel.getId());
            excelBill.setPayrollSettlementId(ythExcelInfoVO.getFormId());
            getBaseMapper().saveJzbBill(tableName, excelBill);
            excelFormLink = new ExcelFormLink();
            excelFormLink.setId(excelBill.getId());
            excelFormLink.setFormId(excelForm.getFormId());
            excelFormLinkMapper.insert(excelFormLink);
        }
        List<ExcelValue> excelValues = singleExcelToValue(excelFormLink, excelForm, excelColumnList, multipartFile);
        getBaseMapper().saveValueList(valueTableName, excelValues);
        return JSON.toJSONString(YthResult.builder().successIds(Stream.of(ythExcelInfoVO.getFormId()).collect(Collectors.toList())));
    }

    private int quarterToMonth(int quarter){
        if (quarter == 1){
            return 3;
        }
        if (quarter == 2){
            return 6;
        }
        if (quarter == 3){
            return 9;
        }
        if (quarter == 4){
            return 12;
        }
        return 0;
    }

    private List<ExcelFormLink> getExcelFormLinkByExcelBill(List<ExcelForm> excelFormList, ExcelBill excelBill,  String valueTableName){
        List<ExcelFormLink> excelFormLinkList = excelFormLinkMapper.selectList(Wrappers.<ExcelFormLink>lambdaQuery()
                .eq(ExcelFormLink::getId, excelBill.getId()));
        if (CollectionUtil.isEmpty(excelFormLinkList)){
            for (ExcelForm excelForm : excelFormList){
                ExcelFormLink excelFormLink = new ExcelFormLink();
                excelFormLink.setId(excelBill.getId());
                excelFormLink.setFormId(excelForm.getFormId());
                excelFormLinkMapper.insert(excelFormLink);
                excelFormLinkList.add(excelFormLink);
            }
        }else {
            getBaseMapper().deleteValueByLinkIdsAndTableName(excelFormLinkList.stream()
                    .map(ExcelFormLink::getLinkId).collect(Collectors.toList()), valueTableName);
        }
        return excelFormLinkList;
    }

    private List<ExcelValue> singleExcelToValue(ExcelFormLink excelFormLink, ExcelForm excelForm, List<ExcelColumn> excelColumnList, MultipartFile multipartFile){
        Map<Integer, String> columnIdMap = excelColumnList.stream().collect(Collectors.toMap(excelColumn -> excelColumn.getOrdernumber(), ExcelColumn::getColumnId));
        try {
            InputStream stream = multipartFile.getInputStream();
            //接收文件流
            //将流给工作簿
            Workbook book = null;
            try {
                book = new HSSFWorkbook(stream);
            } catch (Exception ex) {
                ex.printStackTrace();
                // 解决read error异常
                stream = multipartFile.getInputStream();
                book = new XSSFWorkbook(stream);
            }
            if (book == null){
                throw new MyRuntimeException("excel文件解析失败");
            }

            List<ExcelValue> reportColumnValues = new ArrayList<>();

            Sheet sheet = book.getSheetAt(0);
            String linkId = excelFormLink.getLinkId();
            //遍历行
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                //遍历列
                for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                    ExcelValue excelValue = new ExcelValue();
                    Integer key = cellNum + 1;
                    if (!columnIdMap.containsKey(key)){
                        continue;
                    }
                    excelValue.setColumnId(columnIdMap.get(key));
                    excelValue.setOrdernumber(rowNum + 1);
                    excelValue.setLinkId(linkId);
                    Cell cell = row.getCell(cellNum);
                    if (null == cell) {
                        continue;
                    }
                    excelValue.setValueId(UuidUtil.get32UUID());
                    excelValue.setValues(YthExcelUtils.getValue(cell, excelForm.getIschange() + ""));
                    reportColumnValues.add(excelValue);
                }
            }
            book.close();
            stream.close();
            return reportColumnValues;
        } catch (IOException e) {
            throw new MyRuntimeException(ExceptionUtils.getFullStackTrace(e));
        }
    }

    private List<ExcelValue> excelToValue(List<ExcelFormLink> excelFormLinkList, List<ExcelForm> excelFormList, Map<String, List<ExcelColumn>> excelColumnMap, MultipartFile multipartFile){
        try {
            InputStream stream = multipartFile.getInputStream();
            //接收文件流
            //将流给工作簿
            Workbook book = null;
            try {
                book = new HSSFWorkbook(stream);
            } catch (Exception ex) {
                ex.printStackTrace();
                // 解决read error异常
                stream = multipartFile.getInputStream();
                book = new XSSFWorkbook(stream);
            }
            if (book == null){
                throw new MyRuntimeException("excel文件解析失败");
            }
            Map<String, ExcelForm> excelFormMap = excelFormList.stream().collect(Collectors.toMap(ExcelForm::getName, Function.identity()));
            Map<String, ExcelFormLink> excelFormLinkMap = excelFormLinkList.stream().collect(Collectors.toMap(ExcelFormLink::getFormId, Function.identity()));
            List<ExcelValue> reportColumnValues = new ArrayList<>();
            for (int i = 0; i < book.getNumberOfSheets(); i++) {
                Sheet sheet = book.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                if (!excelFormMap.containsKey(sheetName)) {
                    continue;
                }

                ExcelForm excelForm = excelFormMap.get(sheetName);
                String linkId = excelFormLinkMap.get(excelForm.getFormId()).getLinkId();
                //遍历行
                Map<Integer, String> columnIdMap = excelColumnMap.get(excelForm.getFormId()).stream().collect(Collectors.toMap(excelColumn -> excelColumn.getOrdernumber(), ExcelColumn::getColumnId));
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (null == row) {
                        continue;
                    }
                    //遍历列
                    for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                        ExcelValue excelValue = new ExcelValue();
                        Integer key = cellNum + 1;
                        if (!columnIdMap.containsKey(key)) {
                            continue;
                        }
                        excelValue.setColumnId(columnIdMap.get(key));
                        excelValue.setOrdernumber(rowNum + 1);
                        excelValue.setLinkId(linkId);
                        Cell cell = row.getCell(cellNum);
                        if (null == cell) {
                            continue;
                        }
                        excelValue.setValueId(UuidUtil.get32UUID());
                        excelValue.setValues(YthExcelUtils.getValue(cell, excelForm.getIschange() + ""));
                        reportColumnValues.add(excelValue);
                    }
                }
            }
            book.close();
            stream.close();
            return reportColumnValues;
        } catch (IOException e) {
            throw new MyRuntimeException(ExceptionUtils.getFullStackTrace(e));
        }
    }
}
